Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.IO
Imports System.Data

Partial Class Mngt_Info_PolicyDetails
    Inherits WebPageCommon

    Dim mSql As String

    Protected Sub PolicySearch1_Policy_Selected() Handles PolicySearch1.Policy_Selected
        Dim strRedirect As String = PolicySearch1.Policy_cd
        Call RenderRpt(strRedirect)
    End Sub

    Private Sub RenderRpt(ByVal strSeqCd As String)
        Dim objReport As New ReportDocument
        Dim mSql As String = ""

        objReport.Load(Server.MapPath("~/Management_Info/PolicyAllDetails.rpt"))

        'Fill Data
        FillMainReport(objReport, strSeqCd)

        FillSection1(objReport, strSeqCd)
        FillSection2(objReport, strSeqCd)
        FillSection3(objReport, strSeqCd)
        FillSection4(objReport, strSeqCd)
        FillSection5(objReport, strSeqCd)
        FillPolicyOwner(objReport, strSeqCd)
        FillPolicyInsured(objReport, strSeqCd)
        FillPreRider(objReport, strSeqCd)
        FillInvestment(objReport, strSeqCd)
        FillBene(objReport, strSeqCd)
        FillTrustee(objReport, strSeqCd)
        FillAttachments(objReport, strSeqCd)
        FillPayment(objReport, strSeqCd)

        Dim strDisplay As String = ""
        Dim i As Short

        For i = 0 To chSection.Items.Count - 1
            If chSection.Items(i).Selected = True Then
                strDisplay &= "1"
            Else
                strDisplay &= "0"
            End If
        Next

        objReport.ParameterFields("paramDisplay").CurrentValues.AddValue(strDisplay)

        Dim expReport As ExportOptions
        expReport = objReport.ExportOptions
        expReport.FormatOptions = New PdfRtfWordFormatOptions 'PDF format option
        expReport.ExportFormatType = ExportFormatType.PortableDocFormat 'Set the format type of document
        Dim req As ExportRequestContext = New ExportRequestContext
        req.ExportInfo = expReport

        'Sets the report to HTML stream
        Dim st As System.IO.Stream
        st = objReport.FormatEngine.ExportToStream(req)

        objReport = Nothing 'Dispose the report

        'Stream content
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"

        Dim b(st.Length) As Byte
        st.Read(b, 0, st.Length) 'Read Stream
        Response.BinaryWrite(b) 'Write Stream
        st.Close() 'Close Stream
        st = Nothing 'Dispose

        Response.End()
    End Sub

    Private Sub FillMainReport(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        Dim dm As New DAOMain.CommonDAO

        Dim mSql As String = "SELECT distinct policy_trans.policy_no, policy_client_dtl.policy_client_last_name + " & _
          "', ' + policy_client_dtl.policy_client_first_name + " & _
          "' ' + policy_client_dtl.policy_client_middle_name AS Name, broker_mstr.broker_desc, " & _
          "policy_client_dtl.policy_client_address + ' ' + policy_client_dtl.policy_client_address2 + " & _
          "' ' + country_city_dtl.city_desc + ' ' + country_mstr.country_desc AS Address, " & _
          "provider_mstr.provider_desc as provider, prdt_mstr.prdt_desc as prdt_desc, " & _
     "policy_client_dtl.tel_no, policy_client_dtl.mobile_no, policy_client_dtl.email_address, policy_client_dtl.fax_no, " & _
     "policy_trans.policy_seq_cd " & _
          "FROM prdt_mstr INNER JOIN broker_mstr INNER JOIN provider_mstr ON broker_mstr.broker_cd = " & _
          "provider_mstr.broker_cd ON prdt_mstr.provider_cd = provider_mstr.provider_cd INNER JOIN " & _
          "policy_client_dtl INNER JOIN policy_trans ON policy_client_dtl.policy_seq_cd = " & _
          "policy_trans.policy_seq_cd INNER JOIN country_city_dtl ON policy_client_dtl.city_cd = " & _
          "country_city_dtl.city_cd INNER JOIN country_mstr ON country_city_dtl.country_cd = " & _
          "country_mstr.country_cd ON prdt_mstr.prdt_cd = policy_trans.prdt_cd " & _
          "WHERE (policy_client_dtl.owner_tag = '1') AND (policy_trans.policy_seq_cd='" & strSeqCd & "')"

        dm.AddParameter(strSeqCd)
        'Dim dt As DataTable = dm.ExecQryProc("sp_policy_client_main_rpt")
        Dim dt As DataTable = dm.ExecQuery(mSql)

        With objReport
            dt.TableName = "mpz_Main"
            .SetDataSource(dt)
        End With

        dm = Nothing
        dt = Nothing
    End Sub

    Private Sub FillSubReport(ByVal objReport As ReportDocument, _
                            ByVal strSubReportName As String, _
                            ByVal strTableName As String, _
                            ByVal mSql As String)

        Dim dm As New DAOMain.CommonDAO

        Dim dt As DataTable = dm.ExecQuery(mSql)
        dt.TableName = strTableName
        objReport.Subreports(strSubReportName).SetDataSource(dt)

        dt = Nothing
        dm = Nothing
    End Sub

    Private Sub FillSubReportSP(ByVal objReport As ReportDocument, _
                            ByVal strSubReportName As String, _
                            ByVal strTableName As String, _
                            ByVal sp_name As String, _
                            ByVal sp_param As Collection)

        Dim dm As New DAOMain.CommonDAO
        Dim i As Short

        For i = 1 To sp_param.Count
            dm.AddParameter(sp_param.Item(i))
        Next

        Dim dt As DataTable = dm.ExecQryProc(mSql)
        dt.TableName = strTableName
        objReport.Subreports(strSubReportName).SetDataSource(dt)

        dt = Nothing
        dm = Nothing
    End Sub

#Region "Sections"

    Private Sub FillSection1(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_sec1 WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Section1.rpt", "vw_rpt_policy_sec1", mSql)
    End Sub

    Private Sub FillSection2(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "SELECT distinct dbo.fn_GetBirthDate(policy_approval_dt) AS Approval_Date, establish_period, commence_age, " & _
                  "insured_amt AS Sum_Insured, contribution_amt AS Min_Contr_Period FROM policy_trans " & _
                  "WHERE (policy_seq_cd = '" & strSeqCd & "')"
        FillSubReport(objReport, "Policy_Section2.rpt", "mpz_Section2", mSql)
    End Sub

    Private Sub FillSection3(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_sec3 WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Section3.rpt", "vw_rpt_policy_sec3", mSql)
    End Sub

    Private Sub FillSection4(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_sec4 WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Section4.rpt", "vw_rpt_policy_sec4", mSql)
    End Sub

    Private Sub FillSection5(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_sec5 WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Section5.rpt", "vw_rpt_policy_sec5", mSql)
    End Sub

    Private Sub FillPolicyOwner(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_client_dtl WHERE policy_seq_cd ='" & strSeqCd & "' And owner_tag = 1"
        FillSubReport(objReport, "Policy_Owner_Info.rpt", "vw_rpt_policy_client_dtl", mSql)
    End Sub

    Private Sub FillPolicyInsured(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_client_dtl WHERE policy_seq_cd ='" & strSeqCd & "' And insured_tag = 1"
        FillSubReport(objReport, "Policy_Insured_Info.rpt", "vw_rpt_policy_client_dtl", mSql)
    End Sub

    Private Sub FillPreRider(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_prem_rider WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Prem_Rider.rpt", "vw_rpt_prem_rider", mSql)
    End Sub

    Private Sub FillInvestment(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_fund WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Investments.rpt", "vw_rpt_policy_fund", mSql)
    End Sub

    Private Sub FillBene(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "SELECT distinct A.policy_bene_last_name, A.policy_bene_first_name, A.policy_bene_middle_name, dbo.fn_GetBirthDate(A.birthday) AS birthday, " & _
                      " Gender.Lookup_desc AS Gender, Rel.Lookup_desc AS Relationship, A.share, rank.Lookup_desc AS rank" & _
                    " FROM policy_bene_dtl AS A INNER JOIN" & _
                      " lookup_mstr AS Gender ON A.gender = Gender.Lookup_seq INNER JOIN" & _
                      " lookup_mstr AS Rel ON A.relationship = Rel.Lookup_seq INNER JOIN" & _
                      " policy_trans AS B ON A.policy_seq_cd = B.policy_seq_cd INNER JOIN" & _
                      " lookup_mstr AS rank ON A.ranking_cd = rank.Lookup_seq" & _
                    " WHERE B.policy_seq_cd ='" & strSeqCd & "'"

        FillSubReport(objReport, "Policy_Benefs.rpt", "mpz_Benefs", mSql)
    End Sub

    Private Sub FillTrustee(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_trustee WHERE policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Trustees.rpt", "vw_rpt_policy_trustee", mSql)
    End Sub

    Private Sub FillAttachments(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        Dim dm As New DAOMain.CommonDAO

        Dim sComSec_attach As New Data.OleDb.OleDbCommand
        Dim sDAptSec_attach As New Data.OleDb.OleDbDataAdapter(sComSec_attach)
        Dim sDSSec_attach As New Data.DataSet
        Dim sConn As New Data.OleDb.OleDbConnection

        sConn.ConnectionString = dm.g_strConnection

        sComSec_attach.CommandText = "sp_get_policy_attachments"
        sComSec_attach.CommandType = CommandType.StoredProcedure
        sComSec_attach.Parameters.AddWithValue("@policy_no", strSeqCd)

        sComSec_attach.Connection = sConn
        sDAptSec_attach.Fill(sDSSec_attach)
        sDSSec_attach.Tables(0).TableName = "mpz_Attachments"
        objReport.Subreports("Policy_Attachments.rpt").SetDataSource(sDSSec_attach)

        dm = Nothing
        sComSec_attach = Nothing
        sDAptSec_attach = Nothing
        sDSSec_attach = Nothing
    End Sub

    Private Sub FillPayment(ByVal objReport As ReportDocument, ByVal strSeqCd As String)
        mSql = "Select distinct * from vw_rpt_policy_payment WHERE policy_seq_cd ='" & strSeqCd & "' Order by policy_due_dt DESC"
        'mSql = "SELECT distinct" & _
        '        " C.policy_no, " & _
        '        " A.payment_no, " & _
        '        " B.policy_due_dt, " & _
        '        " A.payment_dt, " & _
        '        " curr_lookup.Lookup_desc AS Currency, " & _
        '        " A.payment_amt, C.policy_seq_cd, " & _
        '        " A.payment_seq_cd, " & _
        '        " form_lookup.Lookup_desc AS Payment_Form" & _
        '    " FROM  dbo.payment_dtl AS A INNER JOIN" & _
        '        " dbo.policy_premium_dtl AS B ON A.policy_premium_cd = B.policy_premium_cd INNER JOIN" & _
        '        " dbo.lookup_mstr AS curr_lookup ON B.currency_cd = curr_lookup.Lookup_seq INNER JOIN" & _
        '        " dbo.policy_trans AS C ON A.policy_seq_cd = C.policy_seq_cd AND B.policy_seq_cd = C.policy_seq_cd INNER JOIN" & _
        '        " dbo.lookup_mstr AS form_lookup ON A.payment_form = form_lookup.Lookup_seq" & _
        '    " WHERE C.policy_seq_cd ='" & strSeqCd & "'"
        FillSubReport(objReport, "Policy_Payment.rpt", "vw_rpt_policy_payment", mSql)
    End Sub

#End Region

End Class
